package com.zt.daoImpl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.stereotype.Repository;

import com.zt.dao.HRMonthlyReportDao;
import com.zt.page.Page;
import com.zt.pojo.HRMonthlyReport;
import com.zt.utils.HibernateSessionFactory;
@Repository("hRMonthlyReportDao")
public class HRMonthlyReportDaoImpl extends HibernateSessionFactory implements HRMonthlyReportDao{
	private int a=0;
	@Override
	public Page<HRMonthlyReport> showHR() {
		Session session = getSession();
		Transaction tran = session.beginTransaction();
		Page<HRMonthlyReport> page=new Page();		
		a=1;
		List<HRMonthlyReport> list=new ArrayList<HRMonthlyReport>();
		try{
			String sql="SELECT IFNULL(d.deptno,0) deptno,IFNULL(d.deptname,NULL) deptName,IFNULL(e.WorkerNum,0) empNum,IFNULL(e.TotalWages,0) maxSal,IFNULL(e.AvgWages,0) avgSal,IFNULL(f.Formal,0) formalNum,IFNULL(l.leaveWorking,0) leaveWorkingNum,IFNULL(t.tryout,0) tryOutNum FROM t_dept d LEFT JOIN (SELECT IFNULL(intro,NULL) e_intro,IFNULL(COUNT(empno),0) WorkerNum,IFNULL(SUM(sal),0) TotalWages,IFNULL(AVG(sal),0) AvgWages FROM t_emp GROUP BY intro) e ON e.e_intro=d.deptno LEFT JOIN (SELECT IFNULL(COUNT(empno),0) Formal,IFNULL(intro,NULL) f_intro FROM t_emp WHERE STATUS='正式' GROUP BY intro) f ON f.f_intro=d.deptno LEFT JOIN (SELECT IFNULL(COUNT(empno),0) leaveWorking,IFNULL(intro,NULL) l_intro FROM t_emp WHERE STATUS='离职' GROUP BY intro) l ON l.l_intro=d.deptno LEFT JOIN (SELECT IFNULL(COUNT(empno),0) TryOut,IFNULL(intro,NULL) t_intro FROM t_emp WHERE STATUS='试用' GROUP BY intro) t ON t.t_intro=d.deptno";
			a=2;
			List<Object> result=session.createSQLQuery(sql).list();
			Iterator<Object> it = result.iterator();
			while(it.hasNext()){
				Object[] objs=(Object[])it.next();
				String deptno=objs[0].toString();
				String deptName=objs[1].toString();
				int empNum=Integer.parseInt(objs[2].toString());
				int maxSal=Integer.parseInt(objs[3].toString());
				double avgSal=Double.parseDouble(objs[4].toString());
				int formalNum=Integer.parseInt(objs[5].toString());
				int leaveWorkingNum=Integer.parseInt(objs[6].toString());
				int tryOutNum=Integer.parseInt(objs[7].toString());
				HRMonthlyReport hr=new HRMonthlyReport
						(deptno,deptName,empNum,maxSal,avgSal,formalNum,leaveWorkingNum,tryOutNum);
				System.out.println("hr="+hr);
				list.add(hr);
			}
			a=3;
			page.setList(list);	
			tran.commit();	
			session.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			System.out.println("a="+a);
		}
		return page;
	}
	
}
